package com.cacho.s2b.lesson.learn;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import java.util.HashMap;
import java.util.Map;

/**
 * @Description java连接oracle(ojdbc10)/mysql数据库
 * @Author LingWei
 * @date 2023/12/31
 **/
@Slf4j
public class LearnJavaSql {
    static String jdbcUrl="jdbc:oracle:thin:@HKLPDDS2B2A-SCAN.HK.STANDARDCHARTERED.COM:1622/svc_ngw_gl_sit1.hk.standardchartered.com";
    public static Connection buildConnection(String jdbcUrl) {
        String username="CC_PSS_VIEW";
        String password="Iamview1234#";
        Connection conn = null;
        try{
            if (jdbcUrl.startsWith("jdbc:postgresql:")) {
                Class.forName("org.postgresql.Driver");
            } else if (jdbcUrl.startsWith("jdbc:oracle:")) {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            }
            conn = DriverManager.getConnection(jdbcUrl, username, password);
        } catch(Exception e) {
            log.warn("数据库连接异常信息：{}", e.getMessage());
        }
        return conn;
    }

    public static void main(String[] args) {
        Connection conn = buildConnection(jdbcUrl);
        Map<String, String> queries = new HashMap<>();
        queries.put("test","select acctno,processsys,processctry from cdl_operatingacct_pref where ACCTNO='01700020402SAR'"); // 结束不需要;
        for (Map.Entry<String, String> entry : queries.entrySet()) {
            String sql = entry.getValue();
            try{
                PreparedStatement pst = conn.prepareStatement(sql);
                ResultSet rs = pst.executeQuery();
                ResultSetMetaData meta = pst.getMetaData();
                System.out.println("需要查询字段数量："+meta.getColumnCount());
                System.out.println("需要查询的第一个字段名："+meta.getColumnLabel(1));
                // 遍历查询结果
                while (rs.next()) {
                    System.out.println("获取字段 acctno 的值："+rs.getString("acctno"));
                }
            } catch(SQLException s){
                log.warn("数据库查询异常信息：{}", s.getMessage());
                safeCloseConnection(conn);
            }
        }
    }

    public static void safeCloseConnection(Connection conn){
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            log.warn("数据库连接异常关闭:{}", e.getMessage());
        }
    }

}
